﻿Imports System.Data.OleDb
Public Class Add_an_Experience
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    End Sub

    Protected Sub btn_submit_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btn_submit.Click


        'The following code can be used to Add the experiences into the experiences table in the database.

        Dim myGuid As New System.Guid
        myGuid = Guid.NewGuid()
        Dim newFileName As String
        newFileName = myGuid.ToString() & ".jpg"

        If f_photo.HasFile() = True Then

            f_photo.SaveAs(Request.MapPath("Pictures") & "/" & newFileName)

        End If

        'The new Experience is added to the database.


        Dim oleDbConn1 As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("TravellingAloneConnectionString").ConnectionString)
        Dim SqlString As String = "Insert into experiences(the_author, the_title, the_location, the_time, the_description, the_photo) Values (@f1,@f2,@f3,@f4,@f5,@f6)"
        Dim cmd As OleDbCommand = New OleDbCommand(SqlString, oleDbConn1)
        cmd.CommandType = CommandType.Text
        cmd.Parameters.AddWithValue("@f1", User.Identity.Name)
        cmd.Parameters.AddWithValue("@f2", tb_title.Text)
        cmd.Parameters.AddWithValue("@f3", tb_location.Text)
        cmd.Parameters.AddWithValue("@f4", tb_time.Text)
        cmd.Parameters.AddWithValue("@f5", tb_description.Text)
        cmd.Parameters.AddWithValue("@f6", "Pictures/" & newFileName)
        oleDbConn1.Open()
        cmd.ExecuteNonQuery()
        cmd.Dispose()
        oleDbConn1.Close()

        '(Updated Jan 6 '14) This code can be used to add the tags to the Tag table in the database.

        Dim oleDbConn2 As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("TravellingAloneConnectionString").ConnectionString)
        oleDbConn2.Open()
        Dim addTag1SqlString As String
        addTag1SqlString = "INSERT INTO tags (tag_word) VALUES (@f9)"
        Dim addTag1Cmd As New OleDbCommand(addTag1SqlString, oleDbConn2)
        addTag1Cmd.CommandType = CommandType.Text
        addTag1Cmd.Parameters.AddWithValue("@f9", tag_box1.Text)
        addTag1Cmd.ExecuteNonQuery()
        addTag1Cmd.Dispose()


        Dim addTag2SqlString As String
        addTag2SqlString = "INSERT INTO tags (tag_word) VALUES (@f10)"
        Dim addTag2Cmd As New OleDbCommand(addTag2SqlString, oleDbConn2)
        addTag2Cmd.CommandType = CommandType.Text
        addTag2Cmd.Parameters.AddWithValue("@f10", tag_box2.Text)
        addTag2Cmd.ExecuteNonQuery()
        addTag2Cmd.Dispose()


        Dim addTag3SqlString As String
        addTag3SqlString = "INSERT INTO tags (tag_word) VALUES (@f11)"
        Dim addTag3Cmd As New OleDbCommand(addTag3SqlString, oleDbConn2)
        addTag3Cmd.CommandType = CommandType.Text
        addTag3Cmd.Parameters.AddWithValue("@f11", tag_box3.Text)
        addTag3Cmd.ExecuteNonQuery()
        addTag3Cmd.Dispose()


        'The following code associates these tags with this experience by adding them into the Junction table.

        Dim expId As Integer
        Dim tag1Id As Integer
        Dim tag2Id As Integer
        Dim tag3Id As Integer


        Dim getExpIdSqlString As String = "SELECT ID FROM experiences WHERE the_title = @f2"
        Dim getExpIdCommand As New OleDbCommand(getExpIdSqlString, oleDbConn2)
        getExpIdCommand.Parameters.AddWithValue("@f2", tb_title.Text)
        Dim expIdreader As OleDbDataReader = getExpIdCommand.ExecuteReader()
        While (expIdreader.Read())
            expId = expIdreader.GetInt32(0)
        End While
        expIdreader.Close()
        getExpIdCommand.Dispose()


        Dim getTag1IdSqlString As String = "SELECT ID FROM tags WHERE tag_word = @f9"
        Dim getTag1IdCommand As New OleDbCommand(getTag1IdSqlString, oleDbConn2)
        getTag1IdCommand.Parameters.AddWithValue("@f9", tag_box1.Text)
        Dim tag1reader As OleDbDataReader = getTag1IdCommand.ExecuteReader()
        While (tag1reader.Read())
            tag1Id = tag1reader.GetInt32(0)
        End While
        tag1reader.Close()
        getTag1IdCommand.Dispose()


        Dim getTag2IdSqlString As String = "SELECT ID FROM tags WHERE tag_word = @f10"
        Dim getTag2IdCommand As New OleDbCommand(getTag2IdSqlString, oleDbConn2)
        getTag2IdCommand.Parameters.AddWithValue("@f10", tag_box2.Text)
        Dim tag2reader As OleDbDataReader = getTag2IdCommand.ExecuteReader()
        While (tag2reader.Read())
            tag2Id = tag2reader.GetInt32(0)
        End While
        tag2reader.Close()
        getTag2IdCommand.Dispose()



        Dim getTag3IdSqlString As String = "SELECT ID FROM tags WHERE tag_word = @f11"
        Dim getTag3IdCommand As New OleDbCommand(getTag3IdSqlString, oleDbConn2)
        getTag3IdCommand.Parameters.AddWithValue("@f11", tag_box3.Text)
        Dim tag3reader As OleDbDataReader = getTag3IdCommand.ExecuteReader()
        While (tag3reader.Read())
            tag3Id = tag3reader.GetInt32(0)
        End While
        tag3reader.Close()
        getTag3IdCommand.Dispose()


        Dim addTag1ToJunction As String = "INSERT INTO experiences_tags_junction(experience_fkid, tag_fkid) VALUES (@f2, @f9)"
        Dim addTag1Command As New OleDbCommand(addTag1ToJunction, oleDbConn2)
        addTag1Command.Parameters.AddWithValue("@f2", expId)
        addTag1Command.Parameters.AddWithValue("@f9", tag1Id)
        addTag1Command.ExecuteNonQuery()
        addTag1Command.Dispose()


        Dim addTag2ToJunction As String = "INSERT INTO experiences_tags_junction(experience_fkid, tag_fkid) VALUES (@f2, @f10)"
        Dim addTag2Command As New OleDbCommand(addTag2ToJunction, oleDbConn2)
        addTag2Command.Parameters.AddWithValue("@f2", expId)
        addTag2Command.Parameters.AddWithValue("@f10", tag2Id)
        addTag2Command.ExecuteNonQuery()
        addTag2Command.Dispose()


        Dim addTag3ToJunction As String = "INSERT INTO experiences_tags_junction(experience_fkid, tag_fkid) VALUES (@f2, @f11)"
        Dim addTag3Command As New OleDbCommand(addTag3ToJunction, oleDbConn2)
        addTag3Command.Parameters.AddWithValue("@f2", expId)
        addTag3Command.Parameters.AddWithValue("@f11", tag3Id)
        addTag3Command.ExecuteNonQuery()
        addTag3Command.Dispose()


        '(Updated 27.12.2013) this code takes the user to see their experience that they just uploaded.


        Dim Redirect_Sql_String As String = "Select ID From experiences WHERE the_title = @f7"
        Dim cmd2 As OleDbCommand = New OleDbCommand(Redirect_Sql_String, oleDbConn2)
        cmd2.Parameters.AddWithValue("@f7", tb_title.Text)
        Dim datread As OleDbDataReader
        datread = cmd2.ExecuteReader()
        Dim redirect_param As Integer
        While (datread.Read())
            redirect_param = datread.GetInt32(0)
        End While
        datread.Close()
        oleDbConn2.Close()
        Response.Redirect("Experience_Detail.aspx?ID=" & redirect_param.ToString)

    End Sub
End Class